package netbay;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import util.jQgrid.JQGridRow;

public class UserGroup {
	public static PreparedStatement convertFindPermission(
			String page,
			String limit, String sidx, String sord,Connection con) throws SQLException {

		Integer startList = Integer.parseInt(limit) * (Integer.parseInt(page) - 1);
        Integer endList = Integer.parseInt(limit) * Integer.parseInt(page);
        
		String strOrderBy = " ORDER BY permission." + sidx + " " + sord;
		String strLimit = " LIMIT "+startList.toString()+", "+endList.toString();
		
		String strSql = "select * from permission where enable = 1";
        strSql += strOrderBy;
        strSql += strLimit;
		
        PreparedStatement st = con.prepareStatement(strSql);
        
		return st;

	}
	public static PreparedStatement convertFindPermission(
			Connection con) throws SQLException {

		String strSql = "select * from permission where enable = 1";
        PreparedStatement st = con.prepareStatement(strSql);
        

		return st;

	}
	
	public static PreparedStatement sqlGetUsernameById(Integer id,
			Connection con) throws SQLException {

		String strSql = "select username from user where id = ?";
        PreparedStatement st = con.prepareStatement(strSql);
        st.setInt(1,id);

		return st;

	}
	
	public static PreparedStatement sqlGetGroupById(Integer id,
			Connection con) throws SQLException {

		String strSql = "select name from permission where id = ?";
        PreparedStatement st = con.prepareStatement(strSql);
        st.setInt(1,id);

		return st;

	}
	
	public static PreparedStatement sqlSavePermission(Connection con,
			int id,int tcdTracking,int rdTracking,int tcdBilling,int rdBilling,
			int manageUsers,int manageGroups,int userLog,
			int reTCDTCD,int reJPMCTCD,int reRDRD,int reJPMCRD) throws SQLException {

		PreparedStatement st = con.prepareStatement("UPDATE permission SET " +
        		"tcdTracking = ?, rdTracking = ?,tcdBilling = ?,rdBilling = ?," +
        		"manageUsers = ?,manageGroups = ?,userLog = ?," +
        		"reTCDTCD = ?,reJPMCTCD = ?,reRDRD = ?,reJPMCRD = ? where id = ?");
		st.setInt(1, tcdTracking);
		st.setInt(2, rdTracking);
		st.setInt(3, tcdBilling);
		st.setInt(4, rdBilling);
		st.setInt(5, manageUsers);
		st.setInt(6, manageGroups);
		st.setInt(7, userLog);
		st.setInt(8, reTCDTCD);
		st.setInt(9, reJPMCTCD);
		st.setInt(10, reRDRD);
		st.setInt(11, reJPMCRD);
		st.setInt(12, id);
		return st;

	}
	public static List convertResultsetPermissionToDisplay(ResultSet rs)
	throws SQLException {
		List displayList = new ArrayList();
		JQGridRow row;
		while (rs.next()) {
			row = new JQGridRow();
			List display = new ArrayList();
			display.add(Integer.toString(rs.getInt("id")));
			display.add(rs.getString("name"));
			display.add(((Boolean)rs.getBoolean("tcdTracking")).toString());
			display.add(((Boolean)rs.getBoolean("rdTracking")).toString());
			display.add(((Boolean)rs.getBoolean("tcdBilling")).toString());
			display.add(((Boolean)rs.getBoolean("rdBilling")).toString());
			display.add(((Boolean)rs.getBoolean("manageUsers")).toString());
			display.add(((Boolean)rs.getBoolean("manageGroups")).toString());
			display.add(((Boolean)rs.getBoolean("userLog")).toString());
			display.add(((Boolean)rs.getBoolean("reTCDTCD")).toString());
			display.add(((Boolean)rs.getBoolean("reJPMCTCD")).toString());
			display.add(((Boolean)rs.getBoolean("reRDRD")).toString());
			display.add(((Boolean)rs.getBoolean("reJPMCRD")).toString());
			row.setCell(display);
			displayList.add(row);
		}
		
		return displayList;
	}
	
	public static PreparedStatement sqlDeleteGroup(Integer id,
			Connection con) throws SQLException {
		PreparedStatement st = con
				.prepareStatement("update permission set enable = 0 WHERE id=?");
		
		st.setInt(1,id);
		return st;

	}
	
	public static PreparedStatement sqlDeleteUserByGroup(Integer id,
			Connection con) throws SQLException {
		PreparedStatement st = con
				.prepareStatement("update user set enable = 0 WHERE idGroup=?");
		
		st.setInt(1,id);
		return st;

	}
	
	public static PreparedStatement convertFindUserLog(String fromTime,String toTime,String page,
			String limit, String sidx, String sord,Connection con) throws SQLException {
		Integer startList = Integer.parseInt(limit) * (Integer.parseInt(page) - 1);
        Integer endList = Integer.parseInt(limit) * Integer.parseInt(page);
        
        PreparedStatement st = null;
        
		
		String strLimit = " LIMIT "+startList.toString()+", "+endList.toString();
		String strOrderBy = " ORDER BY log." + sidx + " " + sord;
		String strSql = " SELECT log.* ,user.username from log left join user on user.id = log.userid where ";

    	String strFromTime = ((fromTime.equals("000000.000")||fromTime==null)?"true and ":"time >= "+fromTime+ " and ");
		String strToTime = 	((toTime.equals("999999.999")||toTime==null)?"true ":"time <= "+toTime);	
        
        strSql += strFromTime;
        strSql += strToTime;
        strSql += strOrderBy;
        strSql += strLimit;
        
        st = con.prepareStatement(strSql);
		return st;

	}
	
	public static PreparedStatement convertCountUserLog(String fromTime,String toTime,Connection con) throws SQLException {

        PreparedStatement st = null;
        
		String strSql = " SELECT count(*) as sum from log where ";

    	String strFromTime = ((fromTime.equals("000000.000")||fromTime==null)?"true and ":"time >= "+fromTime+ " and ");
		String strToTime = 	((toTime.equals("999999.999")||toTime==null)?"true ":"time <= "+toTime);	
        
        strSql += strFromTime;
        strSql += strToTime;
        
        st = con.prepareStatement(strSql);
		
		return st;

	}
	
	public static List convertResultsetUserLogToDisplay(ResultSet rs)
	throws SQLException {
		List displayList = new ArrayList();
		JQGridRow row;

		while (rs.next()) {
			
			row = new JQGridRow();
			List display = new ArrayList();
			String timeStr = rs.getString("time");
			display.add(TransactionManager.convertStrToTmsStr(timeStr));
			
			if(rs.getObject("username")==null)display.add(UserManager.superAdmin);
			else display.add((String)rs.getObject("username"));
			
			
			display.add((String)rs.getObject("action"));
			if(rs.getObject("reference")==null)display.add("");
			else display.add((String)rs.getObject("reference"));
		
			row.setCell(display);
			displayList.add(row);
		}
	
	return displayList;
}
	public static PreparedStatement sqlCheckGroup(String group,
			Connection con) throws SQLException {
		PreparedStatement st = con
				.prepareStatement("SELECT * from permission WHERE name=? and enable =1");
		st.setString(1, group);
		return st;

	}
	
	public static PreparedStatement sqlInsertNewGroup(Connection con,
			String group,int tcdTracking,int rdTracking,int tcdBilling,int rdBilling,
			int manageUsers,int manageGroups,int userLog,
			int reTCDTCD,int reJPMCTCD,int reRDRD,int reJPMCRD) throws SQLException {

		PreparedStatement st = con.prepareStatement("INSERT INTO permission (name,tcdTracking, rdTracking, tcdBilling," +
				"rdBilling,manageUsers,manageGroups,userLog," +
				"reTCDTCD,reJPMCTCD,reRDRD,reJPMCRD,enable)VALUES (" +
        		"?,?,?,?,?," +
        		"?,?,?," +
        		"?,?,?,?,1 )");
		st.setString(1, group);
		st.setInt(2, tcdTracking);
		st.setInt(3, rdTracking);
		st.setInt(4, tcdBilling);
		st.setInt(5, rdBilling);
		st.setInt(6, manageUsers);
		st.setInt(7, manageGroups);
		st.setInt(8, userLog);
		st.setInt(9, reTCDTCD);
		st.setInt(10, reJPMCTCD);
		st.setInt(11, reRDRD);
		st.setInt(12, reJPMCRD);
		return st;

	}
	
	public static PreparedStatement sqlDisplayUser(
			String page,
			String limit, String sidx, String sord,Connection con) throws SQLException {

		Integer startList = Integer.parseInt(limit) * (Integer.parseInt(page) - 1);
        Integer endList = Integer.parseInt(limit) * Integer.parseInt(page);
        
		String strOrderBy = " ORDER BY user." + sidx + " " + sord;
		String strLimit = " LIMIT "+startList.toString()+", "+endList.toString();
		
		String strSql = "SELECT user.id, user.enable, user.username, permission.name, log.time" +
				" FROM (user LEFT JOIN permission ON user.idGroup = permission.id)" +
				" LEFT JOIN log ON user.id = log.userid WHERE" +
				" (log.time = (SELECT MAX( TIME ) FROM log WHERE userid = user.id AND ACTION =  'login' ) " +
				" OR log.time IS NULL) and user.enable =1";
        
        strSql += strOrderBy;
        strSql += strLimit;
        PreparedStatement st = con.prepareStatement(strSql);
		return st;

	}
	public static PreparedStatement sqlDisplayUser(
			Connection con) throws SQLException {

		//String strSql = "SELECT user.*,permission.name FROM user LEFT JOIN permission ON user.idGroup = permission.id";
		String strSql = "SELECT user.id, user.enable, user.username, permission.name, log.time" +
		" FROM (user LEFT JOIN permission ON user.idGroup = permission.id)" +
		" LEFT JOIN log ON user.id = log.userid WHERE" +
		" (log.time = (SELECT MAX( TIME ) FROM log WHERE userid = user.id AND ACTION =  'login' ) " +
		" OR log.time IS NULL) and user.enable =1 ";
		PreparedStatement st = con.prepareStatement(strSql);

		return st;

	}
	
	public static List convertResultsetUserToDisplay(ResultSet rs)
	throws SQLException {
		List displayList = new ArrayList();
		JQGridRow row;
		while (rs.next()) {
			row = new JQGridRow();
			List display = new ArrayList();
			display.add(Integer.toString(rs.getInt("id")));
			display.add(rs.getString("username"));
			display.add(rs.getString("name"));
			
			if(rs.getObject("time") == null){
				display.add("");
			}else{
				String timeDbl = rs.getObject("time").toString();
				Timestamp timeTms = TransactionManager.convertDoubleToTimestamp(timeDbl);
				display.add(TransactionManager.convertTimestampToString(timeTms));
			}

			//display.add(new SimpleDateFormat("yyyy'-'MM'-'dd HH':'mm':'ss").format(rs.getTimestamp("lastLogin")));
			row.setCell(display);
			displayList.add(row);
		}
		
		return displayList;
	}

	public static PreparedStatement sqlGetDataUserById(Integer id,
			Connection con) throws SQLException {
		PreparedStatement st = con
				.prepareStatement("SELECT user.*,permission.name FROM user LEFT JOIN permission ON user.idGroup = permission.id where user.id=?");
		st.setInt(1, id);
		return st;

	}
	public static List convertResultsetUserDataToObject(ResultSet rs)
	throws SQLException {
		//List list = null ;
		ArrayList<String> list=new ArrayList<String>();
		while (rs.next()) {
			list.add(rs.getString("username"));
			list.add(rs.getString("password"));
			list.add(rs.getString("name"));
			list.add(rs.getString("beforeName"));
			list.add(rs.getString("firstName"));
			list.add(rs.getString("lastName"));
			list.add(rs.getString("address"));
			list.add(rs.getString("tel"));
			list.add(rs.getString("fax"));
			list.add(rs.getString("email"));
		}
		
		return list;
	}
	
	public static PreparedStatement sqlCheckOldPassword(Integer id,String oldpass,
			Connection con) throws SQLException {
		PreparedStatement st = con
				.prepareStatement("Select * from user where id = ? && password = ?");
		st.setInt(1, id);
		st.setString(2, oldpass);
		return st;

	}
	
	public static PreparedStatement sqlEditUser(Integer id,String oldpassword,String password,String hashPassword,String rdoRecordType,String firstname,String lastname,
			String address,String tel,String fax,String email,
			Connection con) throws SQLException {
		PreparedStatement st  = null;
		if(oldpassword == ""){
			st = con.prepareStatement("UPDATE user SET beforeName = ? , firstName = ? , lastName = ? ," +
							"address = ? , tel = ? ,fax = ? , email = ? WHERE id=?");
			st.setString(1, rdoRecordType);
			st.setString(2, firstname);
			st.setString(3, lastname);
			st.setString(4, address);
			st.setString(5, tel);
			st.setString(6, fax);
			st.setString(7, email);
			st.setInt(8, id);
		}else{
			if(password != ""){
					st = con.prepareStatement("UPDATE user SET password = ? , beforeName = ? , firstName = ? , lastName = ? ," +
							"address = ? , tel = ? ,fax = ? , email = ? WHERE id=?");
					st.setString(1, hashPassword);
					st.setString(2, rdoRecordType);
					st.setString(3, firstname);
					st.setString(4, lastname);
					st.setString(5, address);
					st.setString(6, tel);
					st.setString(7, fax);
					st.setString(8, email);
					st.setInt(9, id);
				}else{
					st = con.prepareStatement("UPDATE user SET beforeName = ? , firstName = ? , lastName = ? ," +
							"address = ? , tel = ? ,fax = ? , email = ? WHERE id=?");
					st.setString(1, rdoRecordType);
					st.setString(2, firstname);
					st.setString(3, lastname);
					st.setString(4, address);
					st.setString(5, tel);
					st.setString(6, fax);
					st.setString(7, email);
					st.setInt(8, id);
				}
		}
		return st;
	}
	public static PreparedStatement deleteAdmin(String rowdata,
			Connection con) throws SQLException {
		PreparedStatement st = con
				.prepareStatement("update user set enable = ? WHERE id=?");
		st.setString(1, "0");
		st.setInt(2,Integer.parseInt(rowdata));
		return st;

	}
	
	public static PreparedStatement checkUsername(String username,
			Connection con) throws SQLException {
		PreparedStatement st = con
				.prepareStatement("SELECT * from user where username=? and enable = 1");
		st.setString(1, username);
		return st;
	}
	
	public static PreparedStatement sqlFindIdByGroupName(String group,
			Connection con) throws SQLException {
		PreparedStatement st = con
				.prepareStatement("SELECT id from permission WHERE name=?");
		st.setString(1, group);
		return st;

	}
	public static PreparedStatement sqlAddNewUser(String username, String password,Integer id ,String rdoRecordType,
			String firstname,String lastname,String address, 
			String tel,String fax,String email,
			Connection con) throws SQLException {
		PreparedStatement st = con
				.prepareStatement("INSERT INTO user (username, password, idGroup, " +
						"beforeName, firstName, lastName, address," +
						"tel, fax, email, enable)VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		st.setString(1, username);
		st.setString(2, password);
		st.setInt(3, id);
		st.setString(4, rdoRecordType);
		st.setString(5, firstname);
		st.setString(6, lastname);
		st.setString(7, address);
		st.setString(8, tel);
		st.setString(9, fax);
		st.setString(10, email);
		st.setInt(11,1);
		return st;

	}
	public static PreparedStatement loginVerify(String username,String password,
			Connection con) throws SQLException {
		PreparedStatement st = con.
			prepareStatement("SELECT user.id as userId, permission.* FROM user LEFT JOIN permission ON user.idGroup = permission.id where username = ? and password = ? and user.enable = 1");
		st.setString(1, username);
		st.setString(2, password);
		return st;
	}
	
	public static List convertResultsetPermissionToList(ResultSet rs)
	throws SQLException {
		ArrayList<String> list=new ArrayList<String>();
		while (rs.next()) {
			list.add(rs.getString("userId"));
			list.add(rs.getString("name"));
			list.add(rs.getString("tcdTracking"));
			list.add(rs.getString("rdTracking"));
			list.add(rs.getString("tcdBilling"));
			list.add(rs.getString("rdBilling"));
			list.add(rs.getString("manageUsers"));
			list.add(rs.getString("manageGroups"));
			list.add(rs.getString("userLog"));
			list.add(rs.getString("reTCDTCD"));
			list.add(rs.getString("reJPMCTCD"));
			list.add(rs.getString("reRDRD"));
			list.add(rs.getString("reJPMCRD"));
		}
		return list;
	}
	
	public static PreparedStatement sqlLoadDdlGroup(
			Connection con) throws SQLException {

		String strSql = "select id,name from permission where enable = 1";
        PreparedStatement st = con.prepareStatement(strSql);
        

		return st;

	}
	
	public static ArrayList<String> convertResultsetDdlGroupToDisplay(ResultSet rs)
	throws SQLException {
		ArrayList<String> list=new ArrayList<String>();
		while (rs.next()) {
			list.add(rs.getString("id"));
			list.add(rs.getString("name"));
		}
		return list;
	}
	
	public static PreparedStatement sqlLog(
			String userid,String action,String reference,Connection con) throws SQLException {

		String strSql = "INSERT into log (userid,action,reference,time)values (?,?,?,?)";
        PreparedStatement st = con.prepareStatement(strSql);
        
        st.setInt(1, Integer.parseInt(userid));
        st.setString(2, action);
        st.setString(3,reference);
        st.setString(4, TransactionManager.getCurTimeDecimal());
		return st;

	}
	
}
